Relational Algebra in DBMS: Operations with Examples | 您所在的位置:网站首页 › relational algebra pdf › Relational Algebra in DBMS: Operations with Examples |
Relational Algebra
RELATIONAL ALGEBRA is a widely used procedural query language. It collects instances of relations as input and gives occurrences of relations as output. It uses various operations to perform this action. SQL Relational algebra query operations are performed recursively on a relation. The output of these operations is a new relation, which might be formed from one or more input relations. In this tutorial, you will learn: Relational Algebra SELECT(σ) Projection(π) Rename (ρ) Union operation (υ) Set Difference (-) Intersection Cartesian product(X) Join Operations Inner Join: Theta Join: EQUI join: NATURAL JOIN (⋈) OUTER JOIN Left Outer Join(ARelational Algebra devided in various groups Unary Relational Operations SELECT (symbol: σ) PROJECT (symbol: π) RENAME (symbol: ρ) Relational Algebra Operations From Set Theory UNION (υ) INTERSECTION ( ), DIFFERENCE (-) CARTESIAN PRODUCT ( x ) Binary Relational Operations JOIN DIVISIONLet’s study them in detail with solutions: SELECT (σ)The SELECT operation is used for selecting a subset of the tuples according to a given selection condition. Sigma(σ)Symbol denotes it. It is used as an expression to choose tuples which meet the selection condition. Select operator selects tuples that satisfy a given predicate. σp(r) σ is the predicate r stands for relation which is the name of the table p is prepositional logic Example 1 σ topic = "Database" (Tutorials)Output – Selects tuples from Tutorials where topic = ‘Database’. Example 2 σ topic = "Database" and author = "guru99"( Tutorials)Output – Selects tuples from Tutorials where the topic is ‘Database’ and ‘author’ is guru99. Example 3 σ sales > 50000 (Customers) Output – Selects tuples from Customers where sales is greater than 50000 Projection(π)The projection eliminates all attributes of the input relation but those mentioned in the projection list. The projection method defines a relation that contains a vertical subset of Relation. This helps to extract the values of specified attributes to eliminates duplicate values. (pi) symbol is used to choose attributes from a relation. This operator helps you to keep specific columns from a relation and discards the other columns. Example of Projection: Consider the following table CustomerID CustomerName Status 1 Google Active 2 Amazon Active 3 Apple Inactive 4 Alibaba ActiveHere, the projection of CustomerName and status will give Π CustomerName, Status (Customers) CustomerName Status Google Active Amazon Active Apple Inactive Alibaba Active Rename (ρ)Rename is a unary operation used for renaming attributes of a relation. ρ (a/b)R will rename the attribute ‘b’ of relation by ‘a’. Union operation (υ)UNION is symbolized by ∪ symbol. It includes all tuples that are in tables A or in B. It also eliminates duplicate tuples. So, set A UNION set B would be expressed as: The result B.column 2 (B) A ⋈ A.column 2 > B.column 2 (B) column 1 column 2 1 2 EQUI join: When a theta join uses only equivalence condition, it becomes a equi join. For example: A ⋈ A.column 2 = B.column 2 (B) A ⋈ A.column 2 = B.column 2 (B) column 1 column 2 1 1EQUI join is the most difficult operations to implement efficiently using SQL in an RDBMS and one reason why RDBMS have essential performance problems. NATURAL JOIN (⋈)Natural join can only be performed if there is a common attribute (column) between the relations. The name and type of the attribute must be same. Example Consider the following two tables C Num Square 2 4 3 9 D Num Cube 2 8 3 27 C ⋈ D C ⋈ D Num Square Cube 2 4 8 3 9 27 OUTER JOINIn an outer join, along with tuples that satisfy the matching criteria, we also include some or all tuples that do not match the criteria. Left Outer Join(AIn the left outer join, operation allows keeping all tuple in the left relation. However, if there is no matching tuple is found in right relation, then the attributes of right relation in the join result are filled with null values. Consider the following 2 Tables A Num Square 2 4 3 9 4 16 B Num Cube 2 8 3 18 5 75 AIn the right outer join, operation allows keeping all tuple in the right relation. However, if there is no matching tuple is found in the left relation, then the attributes of the left relation in the join result are filled with null values. In a full outer join, all tuples from both relations are included in the result, irrespective of the matching condition. A |
CopyRight 2018-2019 实验室设备网 版权所有 |